ALTER PROC SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM_SITIME ( @ORDER_NO	VARCHAR(50),				
				@CUSTOMER_NAME	VARCHAR(65),
				@CUSTOMER_PO_NO		VARCHAR(50),
				@SHIPPING_TERMS VARCHAR(50),
				@SHIPPING_METHOD	VARCHAR(100),
				@LINE_NO VARCHAR(50),
				@PART_NUMBER		VARCHAR(50), --MFG Part Number
				@LINE_ITEM_QTY		INT,
				@UNIT_PRICE		NUMERIC(15,4),
				@REQUESTED_DOCK_DATE	DATETIME,
				@SCHEDULED_DATE		DATETIME,
				@BOOKING_DATE		DATETIME,
				@END_CUSTOMER		VARCHAR(65),
				@PORTING_STATUS_CODE	INT OUT,
				@SHIP_ADDRESS1		VARCHAR(50),
				@SHIP_ADDRESS2		VARCHAR(50),
				@SHIP_ADDRESS3		VARCHAR(50),
				@SHIP_ADDRESS4		VARCHAR(50),
				@SHIP_CITY		VARCHAR(50),
				@SHIP_STATE		VARCHAR(50),
				@SHIP_ZIPCODE	VARCHAR(50),
				@SHIP_COUNTRY	VARCHAR(50),
				@SHIP_CONTACT	VARCHAR(50),
				@SHIP_PHONE		VARCHAR(50),
				@BILL_ADDRESS1		VARCHAR(50),
				@BILL_ADDRESS2		VARCHAR(50),
				@BILL_ADDRESS3		VARCHAR(50),
				@BILL_ADDRESS4		VARCHAR(50),
				@BILL_CITY		VARCHAR(50),
				@BILL_STATE		VARCHAR(50),
				@BILL_ZIPCODE	VARCHAR(50),
				@BILL_COUNTRY	VARCHAR(50),
				@BILL_CONTACT	VARCHAR(50),
				@BILL_PHONE		VARCHAR(50),
				@LINEITEM_COMMENT VARCHAR(50),
				@SHIP_ADDRESS_CODE VARCHAR(50),
				@BILL_ADDRESS_CODE VARCHAR(50),	
				@CUSTOMER_NUMBER VARCHAR(50) )	

AS
BEGIN
	SET NOCOUNT ON
	--DECLARE @CUSTOMER_NAME VARCHAR(50)
	DECLARE @PRODUCT_ID INT
	DECLARE @CUSTOMER_ID INT
	DECLARE @END_CUSTOMER_ID INT
	DECLARE @END_USER_ID INT
	DECLARE @SHIPPING_METHOD_ID INT
	DECLARE @DELIVERY_TERMS_ID INT
	DECLARE @SALES_ORDER_ID	INT
	DECLARE @DB_CUSTOMER_ID INT
	DECLARE @CUST_DEFAULT_BILLING_ADDRESS_ID INT
	DECLARE @CUST_DEFAULT_SHIPPING_ADDRESS_ID INT
	DECLARE @REGION_ID INT
	DECLARE @COOL_LINEITEM_ID INT
	DECLARE @CCOL_LINEITEM_ID INT
	DECLARE @SO_BOOKING_DATE DATETIME
	DECLARE @SO_SUB_TOTAL FLOAT
	DECLARE @ALARM_ID INT
	DECLARE @SHIPPING_ADDRESS_ID INT
	DECLARE @BILLING_ADDRESS_ID INT
	DECLARE @NEXT_LINE_ITEM_NO INT
	DECLARE @DUMMY_BILLING_ADDRESS_ID INT
	DECLARE @DUMMY_SHIPPING_ADDRESS_ID INT
	DECLARE @DB_C_TYPE_ID INT
	DECLARE @BILLING_CONTACT_PERSON VARCHAR(50)
	DECLARE @IS_CUSTOMER_SHIP_ADDRS INT
	DECLARE @LOCATION_ID INT
	DECLARE @SHIPPING_METHOD_NAME VARCHAR(50)
	DECLARE @SHIPPING_METHOD_ACCOUNT VARCHAR(50)
	DECLARE @COOL_DESCRIPTION VARCHAR(50)

	SET @PORTING_STATUS_CODE = -1 --NOTHING 
	
	IF ( @ORDER_NO IS NULL OR LEN(RTRIM(LTRIM(@ORDER_NO))) = 0 )
	 BEGIN
		SET @PORTING_STATUS_CODE = 2 --SALES ORDER NUMBER REQUIRED
		RETURN
	 END
	--GET MFG PART NUMBER PRODUCT ID
	SET @PRODUCT_ID = NULL
	SELECT @PRODUCT_ID = PRODUCT_ID, @COOL_DESCRIPTION = P_Comments FROM PRODUCT P, PROCESS_STEP PS WHERE PART_NUMBER = @PART_NUMBER AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID and PS_TYPE_NAME = 'FINISHED GOODS'
	IF ( @PRODUCT_ID IS NULL )
	BEGIN
		SELECT @PRODUCT_ID = PRODUCT_ID, @COOL_DESCRIPTION = P_Comments FROM PRODUCT P, PROCESS_STEP PS WHERE P_MARKETING_PART_NUMBER = @PART_NUMBER AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID and PS_TYPE_NAME = 'FINISHED GOODS'
		IF ( @PRODUCT_ID IS NULL )
		BEGIN
			SET @PORTING_STATUS_CODE = 3 --NO FG PART NUMBER FOUND		 
			RETURN
		END
	END
	SET @REGION_ID = 0

	IF ( @SHIPPING_METHOD IS NOT NULL AND LEN(RTRIM(LTRIM(@SHIPPING_METHOD))) > 0 )
	BEGIN 
		-- FedEx # 162849123

		IF( CHARINDEX ( '#', @SHIPPING_METHOD ) > 0 )
		BEGIN
			SET @SHIPPING_METHOD_NAME = LTRIM( RTRIM( SUBSTRING ( @SHIPPING_METHOD, 1, CHARINDEX ( '#',@SHIPPING_METHOD ) -1 ) ) )
			SET @SHIPPING_METHOD_ACCOUNT = LTRIM ( RTRIM ( SUBSTRING ( @SHIPPING_METHOD, CHARINDEX ( '#',@SHIPPING_METHOD )+1, LEN( @SHIPPING_METHOD ) ) ) )

			SELECT @SHIPPING_METHOD_ID = SHIPPING_METHOD_ID FROM SHIPPING_METHODS_TABLE WHERE SM_CUSTOMER_ID = @CUSTOMER_ID AND SHIPPING_METHOD = @SHIPPING_METHOD_NAME AND SHIPPING_ACCOUNT = @SHIPPING_METHOD_ACCOUNT

			IF ( @SHIPPING_METHOD_ID IS NULL )
			BEGIN
				INSERT INTO SHIPPING_METHODS_TABLE( SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT ) VALUES ( @CUSTOMER_ID, @SHIPPING_METHOD_NAME, @SHIPPING_METHOD_ACCOUNT )
				SELECT @SHIPPING_METHOD_ID = @@IDENTITY
			END
			
		END
	END

	SET @DELIVERY_TERMS_ID = NULL
	SELECT @DELIVERY_TERMS_ID = DELIVERY_TERMS_ID FROM DELIVERY_TERMS WHERE DELIVERY_TERMS = ('EXW')

	IF ( @DELIVERY_TERMS_ID IS NULL )
	BEGIN
		INSERT INTO DELIVERY_TERMS( DELIVERY_TERMS ) VALUES ('EXW')
		SELECT @DELIVERY_TERMS_ID = @@IDENTITY
	END
	
	IF ( (SELECT COUNT(*) FROM SHIPPINGTERMS WHERE SHIPPINGTERMS = @SHIPPING_TERMS) = 0 )
	BEGIN
		INSERT INTO SHIPPINGTERMS(SHIPPINGTERMS) VALUES(@SHIPPING_TERMS)
	END


	SET @CUSTOMER_ID = NULL
	SELECT @CUSTOMER_ID = CUSTOMER_ID, @CUST_DEFAULT_BILLING_ADDRESS_ID = C_DEFAULT_BILLING_ADDRESS_ID, @CUST_DEFAULT_SHIPPING_ADDRESS_ID = C_DEFAULT_SHIPPING_ADDRESS_ID FROM CUSTOMER WHERE C_NUMBER = @CUSTOMER_NUMBER AND C_DISABLED = 1
	IF ( @CUSTOMER_ID IS NULL )
	BEGIN
		SELECT @CUSTOMER_ID = CUSTOMER_ID, @CUST_DEFAULT_BILLING_ADDRESS_ID = C_DEFAULT_BILLING_ADDRESS_ID, @CUST_DEFAULT_SHIPPING_ADDRESS_ID = C_DEFAULT_SHIPPING_ADDRESS_ID FROM CUSTOMER WHERE C_NAME = @CUSTOMER_NAME AND C_DISABLED = 1	
		IF @CUSTOMER_ID IS NOT NULL
		BEGIN	
			UPDATE CUSTOMER SET C_NUMBER = @CUSTOMER_NUMBER WHERE CUSTOMER_ID = @CUSTOMER_ID
		END
	END		

	IF ( @CUSTOMER_ID IS NULL )
	BEGIN
		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP, TAG_NAME )
				VALUES ( @BILL_ADDRESS1, @BILL_ADDRESS2, @BILL_ADDRESS3, @BILL_ADDRESS4, @BILL_CITY, @BILL_STATE, @BILL_ZIPCODE, @BILL_COUNTRY, @BILL_PHONE, '', '', '', @BILL_CONTACT, CURRENT_TIMESTAMP, @BILL_ADDRESS_CODE )
		SELECT @CUST_DEFAULT_BILLING_ADDRESS_ID = @@IDENTITY

		IF @BILL_CONTACT IS NOT NULL AND LEN(@BILL_CONTACT) > 1
		BEGIN
			INSERT INTO Address_Contact_Persons_Table ( Address_Id, Contact_person, Phone_number ) VALUES( @CUST_DEFAULT_BILLING_ADDRESS_ID, @BILL_CONTACT, @BILL_PHONE )
		END

		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4,CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP, TAG_NAME )
				VALUES ( @SHIP_ADDRESS1, @SHIP_ADDRESS2, @SHIP_ADDRESS3, @SHIP_ADDRESS4, @SHIP_CITY, @SHIP_STATE, @SHIP_ZIPCODE, @SHIP_COUNTRY, @SHIP_PHONE, '', '', '', @SHIP_CONTACT, CURRENT_TIMESTAMP, @SHIP_ADDRESS_CODE )
		SELECT @CUST_DEFAULT_SHIPPING_ADDRESS_ID = @@IDENTITY

		IF @SHIP_CONTACT IS NOT NULL AND LEN(@SHIP_CONTACT) > 1
		BEGIN
			INSERT INTO Address_Contact_Persons_Table ( Address_Id, Contact_person, Phone_number ) VALUES( @CUST_DEFAULT_SHIPPING_ADDRESS_ID, @SHIP_CONTACT, @SHIP_PHONE )
		END
		
		INSERT INTO CUSTOMER ( C_NAME, C_NUMBER, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED, C_Shipping_Method_1, C_Act_Number_1, C_DELIVERY_TERMS_ID, C_PAY_TERMS) 
			VALUES ( @CUSTOMER_NAME, @CUSTOMER_NUMBER, @CUST_DEFAULT_BILLING_ADDRESS_ID, @CUST_DEFAULT_SHIPPING_ADDRESS_ID,  '', 1, 1, 1, @SHIPPING_METHOD_NAME, @SHIPPING_METHOD_ACCOUNT, @DELIVERY_TERMS_ID, @SHIPPING_TERMS )
		SET @CUSTOMER_ID = @@IDENTITY
		--SET @PORTING_STATUS_CODE = 4 --NO CUSTOMER FOUND
		--RETURN
	END
	--ELSE
	BEGIN
		SELECT @DB_C_TYPE_ID   = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = 'Customer'
		IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @CUSTOMER_ID  AND C_TYPE_ID = @DB_C_TYPE_ID ) = 0 )		
		BEGIN
			INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @CUSTOMER_ID, @DB_C_TYPE_ID ) 
		END
	END

	--SELECT @BILLING_CONTACT_PERSON = CONTACT_PERSON FROM ADDRESS WHERE ADDRESS_ID = @CUST_DEFAULT_BILLING_ADDRESS_ID
	
	SET @END_CUSTOMER_ID = NULL
	IF ( @END_CUSTOMER IS NOT NULL AND LEN(RTRIM(LTRIM(@END_CUSTOMER))) > 0 )
	BEGIN
		SELECT @END_CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @END_CUSTOMER
		IF ( @END_CUSTOMER_ID IS NULL )
		BEGIN
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', CURRENT_TIMESTAMP )
			SELECT @DUMMY_BILLING_ADDRESS_ID = @@IDENTITY
			
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', CURRENT_TIMESTAMP )
			SELECT @DUMMY_SHIPPING_ADDRESS_ID = @@IDENTITY
	
			INSERT INTO CUSTOMER ( C_NAME, C_NUMBER, C_REGION, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED, C_Shipping_Method_1, C_Act_Number_1) 
				VALUES ( @END_CUSTOMER, @END_CUSTOMER, '', @DUMMY_BILLING_ADDRESS_ID, @DUMMY_SHIPPING_ADDRESS_ID,  '', 1, 1, 1, '', '') 
			SELECT @END_CUSTOMER_ID = @@IDENTITY	
		END
		
		IF ( @END_CUSTOMER != @CUSTOMER_NAME )
		BEGIN
			SELECT @DB_C_TYPE_ID   = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = 'End Customer'
			IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @END_CUSTOMER_ID  AND C_TYPE_ID = @DB_C_TYPE_ID ) = 0 )		
			BEGIN
				INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @END_CUSTOMER_ID, @DB_C_TYPE_ID ) 
			END
						
			IF ( ( SELECT COUNT (*) FROM CUSTOMER_ODM_RELATIONSHIP WHERE PARENTCUSTOMER_ID = @CUSTOMER_ID  AND ODMCUSTOMER_ID = @END_CUSTOMER_ID ) = 0 )		
			BEGIN
				INSERT INTO CUSTOMER_ODM_RELATIONSHIP ( PARENTCUSTOMER_ID, ODMCUSTOMER_ID, RELATIONSHIPSTATUS_ID ) VALUES ( @CUSTOMER_ID, @END_CUSTOMER_ID, 1 ) 
			END
		END
	END

	SELECT @SHIPPING_ADDRESS_ID = ADDRESS_ID FROM  ADDRESS WHERE TAG_NAME = @SHIP_ADDRESS_CODE AND ADDRESS_ID IN ( SELECT A.ADDRESS_ID FROM CUSTOMER C LEFT OUTER JOIN CUSTOMER_SHIPPING_ADDRESS_TABLE CSAT ON CSAT.CUSTOMER_ID = C.CUSTOMER_ID JOIN ADDRESS A ON A.ADDRESS_ID = CSAT.ADDRESS_ID OR A.ADDRESS_ID = C.C_DEFAULT_SHIPPING_ADDRESS_ID WHERE C.CUSTOMER_ID = @CUSTOMER_ID )

	IF ( @SHIPPING_ADDRESS_ID IS NULL )
	BEGIN
		SELECT TOP 1 @SHIPPING_ADDRESS_ID = ADDRESS_ID FROM  ADDRESS WHERE ADDRESS_1 = @SHIP_ADDRESS1 AND ISNULL(ADDRESS_2,'') = ISNULL(@SHIP_ADDRESS2,'') AND ISNULL(ADDRESS_3,'') = ISNULL(@SHIP_ADDRESS3,'') AND ISNULL(ADDRESS_4,'') = ISNULL(@SHIP_ADDRESS4,'')
								AND ISNULL(CITY,'') = ISNULL(@SHIP_CITY,'') AND ISNULL(STATE,'') = ISNULL(@SHIP_STATE,'') AND ISNULL(ZIPCODE,'') = ISNULL(@SHIP_ZIPCODE,'') AND ISNULL(COUNTRY,'') = ISNULL(@SHIP_COUNTRY,'')
								AND ADDRESS_ID IN ( SELECT A.ADDRESS_ID FROM CUSTOMER C LEFT OUTER JOIN CUSTOMER_SHIPPING_ADDRESS_TABLE CSAT ON CSAT.CUSTOMER_ID = C.CUSTOMER_ID JOIN ADDRESS A ON A.ADDRESS_ID = CSAT.ADDRESS_ID OR A.ADDRESS_ID = C.C_DEFAULT_SHIPPING_ADDRESS_ID WHERE C.CUSTOMER_ID = @CUSTOMER_ID )
                                AND ISNULL(TAG_NAME,'') = ''
		IF @SHIPPING_ADDRESS_ID IS NOT NULL 
		BEGIN
			UPDATE ADDRESS SET TAG_NAME = @SHIP_ADDRESS_CODE WHERE ADDRESS_ID = @SHIPPING_ADDRESS_ID
		END
	END

	--SELECT TOP 1 @SHIPPING_ADDRESS_ID = A.ADDRESS_ID FROM CUSTOMER C LEFT OUTER JOIN CUSTOMER_SHIPPING_ADDRESS_TABLE CSAT ON CSAT.CUSTOMER_ID = C.CUSTOMER_ID JOIN ADDRESS A ON A.ADDRESS_ID = CSAT.ADDRESS_ID OR A.ADDRESS_ID = C.C_DEFAULT_SHIPPING_ADDRESS_ID WHERE C.CUSTOMER_ID = @END_CUSTOMER_ID --AND A.TAG_NAME = @SHIPPING_LOCATION
	IF ( @SHIPPING_ADDRESS_ID IS NULL )
	BEGIN
		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( @SHIP_ADDRESS1, @SHIP_ADDRESS2, @SHIP_ADDRESS3, @SHIP_ADDRESS4, @SHIP_CITY, @SHIP_STATE, @SHIP_ZIPCODE, @SHIP_COUNTRY, @SHIP_PHONE, '', '', '', @SHIP_CONTACT, CURRENT_TIMESTAMP )
		SELECT @SHIPPING_ADDRESS_ID = @@IDENTITY
		INSERT INTO CUSTOMER_SHIPPING_ADDRESS_TABLE ( CUSTOMER_ID, ADDRESS_ID, TIMESTAMP ) VALUES ( @CUSTOMER_ID, @SHIPPING_ADDRESS_ID, CURRENT_TIMESTAMP )
	END
	ELSE
	BEGIN
        --update address
        UPDATE ADDRESS SET ADDRESS_1 = @SHIP_ADDRESS1, ADDRESS_2 = @SHIP_ADDRESS2, ADDRESS_3 = @SHIP_ADDRESS3, ADDRESS_4 = @SHIP_ADDRESS4,
                 CITY = @SHIP_CITY, STATE = @SHIP_STATE, ZIPCODE = @SHIP_ZIPCODE, COUNTRY = @SHIP_COUNTRY, PHONE_NUMBER = @SHIP_PHONE, CONTACT_PERSON = @SHIP_CONTACT, [TIMESTAMP] = CURRENT_TIMESTAMP
          WHERE ADDRESS_ID = @SHIPPING_ADDRESS_ID
	END
	IF @SHIP_CONTACT IS NOT NULL AND LEN(@SHIP_CONTACT) > 1
	BEGIN
	  IF ( ( SELECT COUNT(*) FROM Address_Contact_Persons_Table WHERE Address_Id = @SHIPPING_ADDRESS_ID AND Contact_person = @SHIP_CONTACT ) = 0 )
	  BEGIN
		  INSERT INTO Address_Contact_Persons_Table ( Address_Id, Contact_person, Phone_number ) VALUES( @SHIPPING_ADDRESS_ID, @SHIP_CONTACT, @SHIP_PHONE )
	  END
	  ELSE
	  BEGIN
		  UPDATE Address_Contact_Persons_Table SET Phone_number = @SHIP_PHONE WHERE Address_Id = @SHIPPING_ADDRESS_ID AND Contact_person = @SHIP_CONTACT
	  END
	END

	SELECT @BILLING_ADDRESS_ID = ADDRESS_ID FROM  ADDRESS WHERE TAG_NAME = @BILL_ADDRESS_CODE AND ADDRESS_ID IN ( SELECT A.ADDRESS_ID FROM CUSTOMER C LEFT OUTER JOIN CUSTOMER_BILLING_ADDRESS_TABLE CSAT ON CSAT.CUSTOMER_ID = C.CUSTOMER_ID JOIN ADDRESS A ON A.ADDRESS_ID = CSAT.ADDRESS_ID OR A.ADDRESS_ID = C.C_DEFAULT_BILLING_ADDRESS_ID WHERE C.CUSTOMER_ID = @CUSTOMER_ID )

	IF ( @BILLING_ADDRESS_ID IS NULL )
	BEGIN
		SELECT TOP 1 @BILLING_ADDRESS_ID = ADDRESS_ID FROM  ADDRESS WHERE ADDRESS_1 = @BILL_ADDRESS1 AND ISNULL(ADDRESS_2,'') = ISNULL(@BILL_ADDRESS2,'') AND ISNULL(ADDRESS_3,'') = ISNULL(@BILL_ADDRESS3,'') AND ISNULL(ADDRESS_4,'') = ISNULL(@BILL_ADDRESS4,'')
								AND ISNULL(CITY,'') = ISNULL(@BILL_CITY,'') AND ISNULL(STATE,'') = ISNULL(@BILL_STATE,'') AND ISNULL(ZIPCODE,'') = ISNULL(@BILL_ZIPCODE,'') AND ISNULL(COUNTRY,'') = ISNULL(@BILL_COUNTRY,'')
								AND ADDRESS_ID IN ( SELECT A.ADDRESS_ID FROM CUSTOMER C LEFT OUTER JOIN CUSTOMER_BILLING_ADDRESS_TABLE CSAT ON CSAT.CUSTOMER_ID = C.CUSTOMER_ID JOIN ADDRESS A ON A.ADDRESS_ID = CSAT.ADDRESS_ID OR A.ADDRESS_ID = C.C_DEFAULT_BILLING_ADDRESS_ID WHERE C.CUSTOMER_ID = @CUSTOMER_ID )
                                AND ISNULL(TAG_NAME,'') = ''
		IF @BILLING_ADDRESS_ID IS NOT NULL
		BEGIN
			UPDATE ADDRESS SET TAG_NAME = @BILL_ADDRESS_CODE WHERE ADDRESS_ID = @BILLING_ADDRESS_ID
		END
	END

	IF ( @BILLING_ADDRESS_ID IS NULL )
	BEGIN
		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( @BILL_ADDRESS1, @BILL_ADDRESS2, @BILL_ADDRESS3, @BILL_ADDRESS4, @BILL_CITY, @BILL_STATE, @BILL_ZIPCODE, @BILL_COUNTRY, @BILL_PHONE, '', '', '', @BILL_CONTACT, CURRENT_TIMESTAMP )
		SELECT @BILLING_ADDRESS_ID = @@IDENTITY
		INSERT INTO CUSTOMER_BILLING_ADDRESS_TABLE ( CUSTOMER_ID, ADDRESS_ID, TIMESTAMP ) VALUES ( @CUSTOMER_ID, @BILLING_ADDRESS_ID, CURRENT_TIMESTAMP )
	END
	ELSE
	BEGIN
        --update address
        UPDATE ADDRESS SET ADDRESS_1 = @BILL_ADDRESS1, ADDRESS_2 = @BILL_ADDRESS2, ADDRESS_3 = @BILL_ADDRESS3, ADDRESS_4 = @BILL_ADDRESS4,
                 CITY = @BILL_CITY, STATE = @BILL_STATE, ZIPCODE = @BILL_ZIPCODE, COUNTRY = @BILL_COUNTRY, PHONE_NUMBER = @BILL_PHONE, CONTACT_PERSON = @BILL_CONTACT, [TIMESTAMP] = CURRENT_TIMESTAMP
          WHERE ADDRESS_ID = @BILLING_ADDRESS_ID
	END
	IF @BILL_CONTACT IS NOT NULL AND LEN(@BILL_CONTACT) > 1
	BEGIN
	  IF ( ( SELECT COUNT(*) FROM Address_Contact_Persons_Table WHERE Address_Id = @BILLING_ADDRESS_ID AND Contact_person = @BILL_CONTACT ) = 0 )
	  BEGIN
		  INSERT INTO Address_Contact_Persons_Table ( Address_Id, Contact_person, Phone_number ) VALUES( @BILLING_ADDRESS_ID, @BILL_CONTACT, @BILL_PHONE )
	  END
	  ELSE
	  BEGIN
		  UPDATE Address_Contact_Persons_Table SET Phone_number = @BILL_PHONE WHERE Address_Id = @BILLING_ADDRESS_ID AND Contact_person = @BILL_CONTACT
	  END
	END
/*
	SET @END_USER_ID = NULL
	IF ( @END_USER IS NOT NULL AND LEN(RTRIM(LTRIM(@END_USER))) > 0 )
	BEGIN
		SELECT @END_USER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @END_USER
		IF ( @END_USER_ID IS NULL )
		 BEGIN
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', CURRENT_TIMESTAMP )
			SELECT @DUMMY_BILLING_ADDRESS_ID = @@IDENTITY
			
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', CURRENT_TIMESTAMP )
			SELECT @DUMMY_SHIPPING_ADDRESS_ID = @@IDENTITY
	
			INSERT INTO CUSTOMER ( C_NAME, C_NUMBER, C_REGION, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED, C_Shipping_Method_1, C_Act_Number_1) 
				VALUES ( @END_USER, @END_USER, '', @DUMMY_BILLING_ADDRESS_ID, @DUMMY_SHIPPING_ADDRESS_ID,  '', 1, 1, 1, '', '') 
			SELECT @END_USER_ID = @@IDENTITY					
		 END
		SELECT @DB_C_TYPE_ID   = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = 'End User'
		IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @END_USER_ID  AND C_TYPE_ID = @DB_C_TYPE_ID ) = 0 )		
		BEGIN
			INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @END_USER_ID, @DB_C_TYPE_ID ) 
		END
					
		IF ( ( SELECT COUNT (*) FROM CUSTOMER_ENDCUSTOMER_RELATIONSHIP WHERE PARENTCUSTOMER_ID = @CUSTOMER_ID  AND ENDCUSTOMER_ID = @END_USER_ID ) = 0 )		
		BEGIN
			INSERT INTO CUSTOMER_ENDCUSTOMER_RELATIONSHIP ( PARENTCUSTOMER_ID, ENDCUSTOMER_ID, RELATIONSHIPSTATUS_ID ) VALUES ( @CUSTOMER_ID, @END_USER_ID, 1 ) 
		END
	END*/

	

/*	IF ( @SALES_PERSON IS NOT NULL AND LEN(RTRIM(LTRIM(@SALES_PERSON))) > 0 )
	 BEGIN
		IF ( (SELECT COUNT(*) FROM SALES_PERSONS WHERE SALESPERSON_NAME = @SALES_PERSON) = 0 )
		 BEGIN
			DECLARE @DUMMY_ADDRS_ID INT
			DECLARE @SALESPERSON_ID INT
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', CURRENT_TIMESTAMP )
			SELECT @DUMMY_ADDRS_ID = @@IDENTITY
			INSERT INTO SALES_PERSONS(SALESPERSON_NAME, DEACTIVATED, ADDRESS_ID, SALES_PERCENT, REGION_ID) VALUES (@SALES_PERSON, 0 , @DUMMY_ADDRS_ID, 0.0, @REGION_ID)
			SELECT @SALESPERSON_ID = @@IDENTITY			
		 END
		SELECT @SALESPERSON_ID = SALESPERSON_ID FROM SALES_PERSONS WHERE SALESPERSON_NAME = @SALES_PERSON
		EXEC SP_SAVE_SALESPERSON_LIST @CUSTOMER_ID, @SALESPERSON_ID
	 END
*/
	IF ( @BOOKING_DATE IS NULL )
	BEGIN
		SET @BOOKING_DATE = CONVERT(VARCHAR,CURRENT_TIMESTAMP,101)
	END

	SET @IS_CUSTOMER_SHIP_ADDRS = 0

	DECLARE @LINEITEM_STATUS VARCHAR(50)
	DECLARE @FINANCE_HOLD  VARCHAR(10)
	IF @LINEITEM_COMMENT = 'DO NOT SHIP UNTIL PAYMENT'
	BEGIN
			SET @LINEITEM_STATUS = 'HOLD'
			SET @FINANCE_HOLD = 'TRUE'
	END
	ELSE
	BEGIN
		SET @LINEITEM_STATUS = 'APPROVED'
	END

	SET @SALES_ORDER_ID = NULL
	SELECT @SALES_ORDER_ID = SALESORDER_ID, @DB_CUSTOMER_ID = CUSTOMER_ID, @SO_BOOKING_DATE = SO_BOOKING_DATE FROM SALES_ORDER WHERE SALESORDER_NO = RTRIM(LTRIM(@ORDER_NO))
	IF ( @SALES_ORDER_ID IS NULL ) --NEW SALES ORDER TO BE CREATED
	BEGIN
		INSERT INTO Alarm (Alarm_Name, Alarm_Type, Created_By, Created_On, Expires_On, Current_State, Message, Priority, Respond_Before, Last_Updated_By, Last_Modified_Date) 
 			VALUES ('Sales Order Notification - SalesOrder Number: '+@ORDER_NO, 'SALES_ORDER', 'admin', @BOOKING_DATE, DATEADD(Month, 3, @BOOKING_DATE), 'UPDATED', '', 'HIGH', '1970-01-01 00:00:00.000', 'admin', @BOOKING_DATE)
		SELECT @ALARM_ID = @@IDENTITY

		INSERT INTO SALES_ORDER(SALESORDER_NO, CUSTOMER_ID, SO_CONTACT_PERSON, SO_SALES_PERSON, SO_CUSTOMER_PO_NUMBER, SO_BOOKING_DATE, SO_SHIPPING_METHOD, SO_SHIPPING_ACCOUNT, SO_DELIVERY_TERMS_ID, SO_BILLING_ADDRESS_ID, SO_SHIPPING_ADDRESS_ID, SO_COMMENTS, SO_SHIPPING_INSTRUCTIONS, SO_ENDUSER, SO_STATUS, SO_IS_SAMPLE, SO_IS_CANCEL, SO_IS_PENDING, SO_IS_SHIPPED, SO_ENTERED_BY_ID, SO_ENTERED_BY_NAME, ALARM_ID, TIMESTAMP, REGIONAL_REP, SO_IS_CUSTOMER_SHIP_ADDRESS, SO_FREIGHT_INSTRUCTIONS, SHIPPING_TERMS, DELIVERY_TERMS, SO_MISC_NAME, SO_SHOW_PRO_FORMA_INVOICE, SO_BILLING_CONTACT_PERSON, SO_SHIPPING_CONTACT_PERSON, SO_SALES_REP, SO_ODM_ID, SO_ENDCUSTOMER_ID, SO_IS_CONSIGNMENT  )
			VALUES(RTRIM(LTRIM(@ORDER_NO)), @CUSTOMER_ID, '', '', @CUSTOMER_PO_NO, @BOOKING_DATE, ISNULL(@SHIPPING_METHOD_NAME,''), ISNULL(@SHIPPING_METHOD_ACCOUNT,''), @DELIVERY_TERMS_ID, @BILLING_ADDRESS_ID, @SHIPPING_ADDRESS_ID, '', '', '', @LINEITEM_STATUS, 1, 1, 0, 1, 'admin', 'System Administrator', @ALARM_ID, current_timestamp, '', @IS_CUSTOMER_SHIP_ADDRS, '', @SHIPPING_TERMS, 'EXW', 'Misc', 0, ISNULL(@BILL_CONTACT,''), @SHIP_CONTACT, '', @END_CUSTOMER_ID, @END_USER_ID, 1 )
		SELECT @SALES_ORDER_ID = @@IDENTITY

		INSERT INTO CHANGE_SALES_ORDER(SALESORDER_ID, CHANGENO, CUSTOMER_ID, CSO_CUSTOMER_PO_NUMBER, CSO_SHIPPING_METHOD, CSO_SHIPPING_ACCOUNT, CSO_BILLING_ADDRESS_ID, CSO_SHIPPING_ADDRESS_ID, CSO_STATUS, CSO_BOOKING_DATE, CSO_IS_SAMPLE, CSO_IS_CANCEL, CSO_IS_PENDING, CSO_IS_SHIPPED, CSO_ENTERED_BY_ID, CSO_ENTERED_BY_NAME, Timestamp, SHIPPING_TERMS, DELIVERY_TERMS, CSO_MISC_NAME, CSO_SHOW_PRO_FORMA_INVOICE, CSO_ODM_ID, CSO_IS_CUSTOMER_SHIP_ADDRESS, CSO_ENDCUSTOMER_ID, CSO_DELIVERY_TERMS_ID )
			VALUES (RTRIM(LTRIM(@SALES_ORDER_ID)), 0, @CUSTOMER_ID, @CUSTOMER_PO_NO, ISNULL(@SHIPPING_METHOD_NAME,''), ISNULL(@SHIPPING_METHOD_ACCOUNT,''), @BILLING_ADDRESS_ID, @SHIPPING_ADDRESS_ID, @LINEITEM_STATUS, @BOOKING_DATE, 1, 1, 0, 1, 'admin', 'System Administrator', @BOOKING_DATE, @SHIPPING_TERMS, 'EXW', 'Misc', 0, @END_CUSTOMER_ID, 0, @END_USER_ID, @DELIVERY_TERMS_ID )
		SET @SO_BOOKING_DATE = CAST( @BOOKING_DATE AS DATETIME )
	END
	ELSE --SALES ORDER EXISTS NEED TO UPDATE 
	BEGIN		
		UPDATE SALES_ORDER SET SO_CUSTOMER_PO_NUMBER = @CUSTOMER_PO_NO, 
			SO_SHIPPING_METHOD = ISNULL(@SHIPPING_METHOD_NAME,''), SO_SHIPPING_ACCOUNT = ISNULL(@SHIPPING_METHOD_ACCOUNT,''),SO_DELIVERY_TERMS_ID = @DELIVERY_TERMS_ID, 
			SO_BILLING_ADDRESS_ID = @BILLING_ADDRESS_ID, SO_SHIPPING_ADDRESS_ID = @SHIPPING_ADDRESS_ID,
			SHIPPING_TERMS = @SHIPPING_TERMS, DELIVERY_TERMS = 'EXW', SO_ODM_ID = @END_CUSTOMER_ID,
			SO_ENDCUSTOMER_ID = @END_USER_ID, SO_IS_CUSTOMER_SHIP_ADDRESS = @IS_CUSTOMER_SHIP_ADDRS, SO_BILLING_CONTACT_PERSON = ISNULL(@BILL_CONTACT,''), SO_SHIPPING_CONTACT_PERSON = ISNULL(@SHIP_CONTACT,''),
			SO_IS_CONSIGNMENT = 1, TIMESTAMP = current_timestamp
			WHERE SALESORDER_ID = @SALES_ORDER_ID
	 END

	SET @CCOL_LINEITEM_ID = NULL
	SET @NEXT_LINE_ITEM_NO = NULL

	SELECT @COOL_LINEITEM_ID = LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID AND COOL_Shipment_No = @LINE_NO
	IF ( @COOL_LINEITEM_ID IS NULL ) --NO LINEITEM PRESENT IN COOL TABLE NEED TO INSERT 
	BEGIN
		IF ( (CONVERT(FLOAT,@LINE_NO)/CONVERT(FLOAT,16384)) > (@LINE_NO/16384))
		BEGIN
			SELECT @NEXT_LINE_ITEM_NO = MAX(COOL_LINE_ITEM_NO) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID
			SET @NEXT_LINE_ITEM_NO = @NEXT_LINE_ITEM_NO + 20
		END
		ELSE
		BEGIN
			SET @NEXT_LINE_ITEM_NO = (@LINE_NO/16384)
		END

		INSERT INTO CUSTOMER_OPEN_ORDER_LINEITEM( SALESORDER_ID, COOL_LINE_ITEM_NO, COOL_PO_LINE_ITEM_NO, PRODUCT_ID, COOL_QTY, COOL_UNIT_COST, COOL_AMOUNT, COOL_REQUESTED_DATE, COOL_ACT_REQ_DATE, 
			COOL_BOOKING_DATE, COOL_SCHEDULE_DATE, COOL_STATUS, COOL_ADDED_BY_ID, COOL_ADDED_BY_NAME,  TIMESTAMP, COOL_IS_CANCEL, COOL_DESCRIPTION, COOL_Shipment_No )
			VALUES ( @SALES_ORDER_ID, @NEXT_LINE_ITEM_NO, @NEXT_LINE_ITEM_NO, @PRODUCT_ID, @LINE_ITEM_QTY, @UNIT_PRICE, (@LINE_ITEM_QTY*@UNIT_PRICE), @REQUESTED_DOCK_DATE, @REQUESTED_DOCK_DATE, 
			@BOOKING_DATE, @SCHEDULED_DATE, @LINEITEM_STATUS, 'admin', 'admin', current_timestamp, 1, @COOL_DESCRIPTION, @LINE_NO )
		SELECT @COOL_LINEITEM_ID = @@IDENTITY

		INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
				VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'MARKETING', 'admin', 0, @BOOKING_DATE)
		INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
				VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'SALES', 'admin', 0, @BOOKING_DATE)
		
		IF @FINANCE_HOLD = 'TRUE'
		BEGIN
				INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
				VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'FINANCE', 'admin', 1, @BOOKING_DATE)
		END
		ELSE
		BEGIN
				INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
				VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'FINANCE', 'admin', 0, @BOOKING_DATE)		
		END
			
		INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
				VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'OPERATIONS', 'admin', 0, @BOOKING_DATE)

		INSERT INTO CUSTOMER_CHANGE_ORDER_LINEITEM( LINEITEM_ID, SALESORDER_ID, CHANGENO, CCO_LINE_ITEM_NO, PRODUCT_ID, CCO_QTY, CCO_UNIT_COST, CCO_AMOUNT, CCO_REQUESTED_DATE, 
			CCO_BOOKING_DATE, CCO_SCHEDULE_DATE, CCO_STATUS, CCO_CHANGE_BY_ID, CCO_CHANGE_BY_NAME,  TIMESTAMP)
			VALUES ( @COOL_LINEITEM_ID, @SALES_ORDER_ID, 0, @NEXT_LINE_ITEM_NO, @PRODUCT_ID, @LINE_ITEM_QTY, @UNIT_PRICE, (@LINE_ITEM_QTY*@UNIT_PRICE), @REQUESTED_DOCK_DATE, 
			@BOOKING_DATE, @SCHEDULED_DATE, @LINEITEM_STATUS, 'admin', 'admin', current_timestamp )		

		--INSERT INTO TEMP_BACKLOG_PORTING_TABLE(SALESORDER_ID, LINEITEM_ID, LINE_ITEM_NO,SHIPMENT_NO) VALUES (@SALES_ORDER_ID, @COOL_LINEITEM_ID, @LINE_NO, @SHIPMENT_NO)
		SET @PORTING_STATUS_CODE = 0 --LINEITEM CREATED
 	 END
	 ELSE --LINEITEM EXISTS IN COOL TABLE NEED TO UPDATE IT
	 BEGIN
		UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET PRODUCT_ID = @PRODUCT_ID, COOL_QTY = @LINE_ITEM_QTY, COOL_UNIT_COST = @UNIT_PRICE, COOL_AMOUNT = (@LINE_ITEM_QTY*@UNIT_PRICE), COOL_REQUESTED_DATE = @REQUESTED_DOCK_DATE,COOL_ACT_REQ_DATE = @REQUESTED_DOCK_DATE,
			COOL_BOOKING_DATE = @BOOKING_DATE, COOL_SCHEDULE_DATE = @SCHEDULED_DATE, COOL_ADDED_BY_ID = 'admin', COOL_ADDED_BY_NAME = 'admin',
			COOL_STATUS = @LINEITEM_STATUS, TIMESTAMP = current_timestamp
			WHERE LINEITEM_ID = @COOL_LINEITEM_ID
		
		IF ( @LINEITEM_STATUS <> 'HOLD' )
		 BEGIN
			UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '', IS_APPROVED = 0, TIMESTAMP = @BOOKING_DATE WHERE LINEITEM_ID = @COOL_LINEITEM_ID
		 END
		 ELSE
		 BEGIN
			IF @FINANCE_HOLD = 'TRUE'
			BEGIN
				UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '0', IS_APPROVED = 1, TIMESTAMP = @BOOKING_DATE WHERE LINEITEM_ID = @COOL_LINEITEM_ID AND HOLD_BY_GROUP = 'FINANCE'
			END
			/*ELSE
			BEGIN
				UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '0', IS_APPROVED = 1, TIMESTAMP = @BOOKING_DATE WHERE LINEITEM_ID = @COOL_LINEITEM_ID 
			END*/
		 END
		SET @PORTING_STATUS_CODE = 1 --LINEITEM UPDATED
 	 END
	 
	--UPDATING SO SUB TOTAL
	SELECT @SO_SUB_TOTAL = SUM(ISNULL(COOL_AMOUNT,0)) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID
	IF ( (SELECT COUNT(*) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE COOL_STATUS='HOLD' AND SALESORDER_ID = @SALES_ORDER_ID AND CONVERT(VARCHAR,[TIMESTAMP],101) = CONVERT(VARCHAR,@BOOKING_DATE,101) ) > 0 ) --HOLD LINE ITEMS EXISTS WHICH ARE UPDATED TODAY SINCE WE ARE MAINTAINING SNAPSHOT OF THE SHEET
	 BEGIN
		UPDATE SALES_ORDER SET SO_SUB_TOTAL = ISNULL(@SO_SUB_TOTAL,0), SO_STATUS='HOLD' WHERE SALESORDER_ID = @SALES_ORDER_ID
	 END
	ELSE
	 BEGIN
		UPDATE SALES_ORDER SET SO_SUB_TOTAL = ISNULL(@SO_SUB_TOTAL,0), SO_STATUS='APPROVED' WHERE SALESORDER_ID = @SALES_ORDER_ID
	 END

END